Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct

Question

I am pretty new to Hibernate. I found out that we can get distinct result using following two different ways. Could any one tell me what is the difference between them? When to use one over other?

Projections.distinct(Projections.property("id"));

vs

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Nestorius

Answer 1

While similar names, the usage is different.

I. Projections.distinct(Projections.property("id"));

this statement would be translated into SQL Statement. It will be passed to DB Engine and executed as a SQL DISTINCT. See:

• 17.9. Projections, aggregation and grouping

so e.g. this example:

List results = session.createCriteria(Cat.class)
    .setProjection( Projections.projectionList()
        .add( Projections.distinct(Projections.property("id")) )
    )
    .list();

would seems like:

SELECT DISTINCT(cat_id) FROM cat_table

II. criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

This statement is executed ex-post. Once the SQL query from DB engine is returned and Hibernate iterates the result set to convert that into list of our entities.

But is it needed always? NO, mostly this is not needed.

The only case, when we MUST to use that, if there is an association in the query - JOINING the one-to-many end.

Because if we do have one cat and its two kittens, this would return two rows, while cat is only one:

SELECT cat.*, kitten.*
FROM cat_table as cat 
  INNER JOIN kitten_table kitten ON kitten.cat_id = cat.cat_id

So, the statement at the end of the criteriaQuery:

... // criteriaQuery joining root and some one-to-many
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)

would result in a list with only one cat.

Betseybetsy

Thank you very much for your clear answer. It is very well explained. I almost finished reading Beginning Hibernate book, but I still couldn't figure out about Criteria.DISTINCT_ROOT_ENTITY. Your answer with an example made me clear. – Nestorius

i have a doubt, how can u make in the first example it returns, the class, and not only the id, do u know what i mean? thanks. – Dower

We can use the distinct id and use it using HQL to get distinct rows from a class, I don't think it is good idea in terms of the performance though. – Ravioli

But will the only one cat have both kitten? – Raskind

I know this is very old, but can you explain what DISTINCT_ROOT_ENTITY does in actual SQL terms? The way you described it in your answer is "You have one row A which is 1-n and results in two rows B, you apply DISTINCT_ROOT_ENTITY and it magically results in one row B" - how? I'm honestly quite stumped on how to replicate this in a query I'm building (has nothing to do with java) – Maible

Answer 2

From docs: DISTINCT_ROOT_ENTITY Each row of results is a distinct instance of the root entity

distinct() selects distinct by property, in you case by identifier

Pitarys

Thanks for your response. I understood second line but because of lack of knowledge about root entity, I didn't understand first line completely. It was still very helpful. – Nestorius